<?php

namespace app\models;

use yii;
use yii\db\ActiveRecord;
use yii\helpers\ArrayHelper;
use app\common\Functions;



class ProdInst extends ActiveRecord
{
    public $qCount;
    public $lendedPrice;  // 已租均价
    public $lendedArea;  // 已租面积
    public $totalArea;   //  总面积
    public $vacancyArea;  // 空置房间
    public $lendedState;
    public $ownCustName;
    public $useCustName;
    public $parentProdName;
    public $attrcode;
    public $province;
    public $areas;
    public $city;
    public $attrSpecs;
    public $localProdAttrsInsts;
    public $prodAttrInstCode;

    public $orderStaffID;      // 关联的订单员工号
    public $orderStaffName;   // 关联的订单员工ID

    public $orderRemark;      // 订单描述
    public $region_name;//街道
    public $phone;
    public $staffName;
    public $staff_id;



    public function rules()
    {
        return [
            ['id', 'integer'],
            ['orderStaffID', 'integer'],
            ['orderRemark',  'string'],

            ['parent_id', 'integer'],
            ['prod_name', 'string'],
            ['region_id', 'integer'],
            ['use_cust_id', 'integer'],
            ['own_cust_id', 'integer'],
            ['prod_spec_id', 'integer'],
            ['attrSpecs', 'validateProdAttr'],
            ['parentProdInsts', 'validateProdAttr'],
            ['prodAttrInsts', 'validateProdAttr'],
            ['localProdAttrsInsts', 'validateProdAttr', 'message' => '请填写名称.'],
            ['parentProdName', 'string', 'length' => [1, 128]],
            ['ownCustName', 'string', 'length' => [1, 128]],
            ['state', 'string', 'length' => [1, 8]],
            ['staff_id', 'validateProdAttr'],

        ];

    }



    public function getUseCustName()
    {
        return $this->useCustName;
    }

    public function getOrderRemark()
    {
        return $this->orderRemark;
    }
    public function getOrderStaffID()
    {
        return $this->orderStaffID;
    }

    public function getOrderStaffName()
    {
        return $this->orderStaffName;
    }

    public function getLendedArea()
    {

    }

    public function getTotalArea()
    {
        return $this->totalArea;
    }

    public function getVacancyArea()
    {
        return $this->vacancyArea;
    }

    public function getLendedState()
    {
        $lendedState = '1002';
        return  $lendedState;
    }

    public function getParentProdName()
    {
        return $this->parentProdName;
    }

    public function getOwnCustName()
    {
        return $this->ownCustName;
    }

    public function validateProdAttr($attribute, $params)
    {
//        return true;
    }

    public function getAttrcode()
    {
        return $this->attrcode;
    }

    public function getProdAttrInstCode()
    {
        return $this->prodAttrInstCode;
    }

    public function getProvince()
    {
        return $this->province;
    }

    public function getCity()
    {
        return $this->city;
    }

    public function getArea()
    {
        return $this->areas;
    }

    public function getUseCustCount($id, $prod_spec_id=null)
    {
        $sql  = 'SELECT use_cust_id FROM  ';
        $sql .= ProdInst::tableName();

        if ($prod_spec_id==10002)
        {
            $sql .= ' as a  WHERE  id=:1 and state=:2 and use_cust_id is not null GROUP BY use_cust_id';
        }else{
            $sql .= ' as a  WHERE parent_id=:1 and state=:2 and use_cust_id is not null GROUP BY use_cust_id';
        }


        $useCustCount = Yii::$app->db->createCommand($sql)
        ->bindValue(':1', $id)
        ->bindValue(':2', '1002')
        ->query()->rowCount;

        return $useCustCount;
    }

    // 总面积 取出属性编码为8的记录
    public function getBuildAreas()
    {
        $where = [];
        $where['attr_spec_id'] = 8;

        return  $this->hasOne(ProdAttrInst::className()  , ['prod_id' => 'id'])->viaTable(ProdInst::tableName()  ,  ['parent_id' => 'id'])->select(['*', 'sum(truncate(content,2)) as sumContent'])->where($where);


       // return $this->hasOne(ProdAttrInst::className()  , ['prod_id' => 'id'])->viaTable(ProdInst::tableName()  ,  ['parent_id ' => 'id'])->select(['*', 'sum(content) as sumContent'])->where($where);
    }

    public function getBuildAreasFirst()
    {
        $where = [];
        $where['attr_spec_id'] = 8;


        return $this->hasOne(ProdAttrInst::className()  , ['prod_id' => 'id'])->select(['*', 'sum(truncate(content,2)) as sumContent'])->where($where);


        // return $this->hasOne(ProdAttrInst::className()  , ['prod_id' => 'id'])->viaTable(ProdInst::tableName()  ,  ['parent_id ' => 'id'])->select(['*', 'sum(content) as sumContent'])->where($where);
    }

    public function getLendedProds()
    {
        $where = [];
       // $where['attr_spec_id'] = '8';
        $where['state'] = '1002';



       // $where['a.state'] = '1002';
     //   $where['attr_spec_id'] = '8';
        return $this->hasMany(ProdInst::className(), ['parent_id' => 'id'])->where($where);
      //  return $this->hasMany(GroupUserRelation::tableName(), ['parent_id' => 'id']);
     //   return $this->hasMany(ProdAttrInst::className(), ['prod_id' => 'id'])->onCondition(['state' => '1002'])->where($where);
    }

    // 已租面积
    public function selectLendedAreas($id=null, $prod_spec_id=null)
    {
        $sql  = 'SELECT sum(truncate(content,2)) as sumContent FROM  ';
        $sql .= ProdAttrInst::tableName();
        $sql .= ' as a, ';
        $sql .= ProdInst::tableName() ;

        if ($prod_spec_id==10002)
        {
            $sql .= ' as b WHERE a.attr_spec_id =:attr_spec_id AND a.prod_id = b.id and b.id = :id and b.state = :state ';
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':attr_spec_id', 8)
                ->bindValue(':id',$id)
                ->bindValue(':state', '1002')
                ->queryOne();

        }else{
            $sql .= ' as b WHERE a.attr_spec_id =:attr_spec_id AND a.prod_id = b.id and b.parent_id = :parent_id and b.state = :state ';
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':attr_spec_id', 8)
                ->bindValue(':parent_id',$id)
                ->bindValue(':state', '1002')
                ->queryOne();
        }




//        $query = ProdInst::find()->alias('a');
//        $query->joinWith('prodAttrInsts  AS b');
//        $where = [];
//        $where['a.parent_id'] = $id;
//        $where['a.state'] = '1002';
//        $where['b.attr_spec_id'] = '8';
//
//        $lendedAreas = $query->where($where)->select(['sum(content) as sumLendedContent'])->One();

        return $lendedAreas;

//        return $this->hasOne(ProdAttrInst::className()     , ['prod_id' => 'id'])
//            ->viaTable(ProdInst::tableName()  ,  ['parent_id' => 'id'])
//            ->onCondition(['state' => 1002])
//            ->select(['*', 'sum(content) as sumLendedContent'])->where($where);

       // return $this->hasOne(ProdAttrInst::className()     , ['prod_id' => 'id']) ->select(['*', 'sum(content) as sumLendedContent'])->where($where);

       // return $this->hasOne(ProdInst::className()  , ['parent_id' => 'id'])->select(['*', 'sum(content) as sumContent'])->where($where);
    }

    // 可招商面积
    public function selectCanvassAreas($id, $prod_spec_id=null)
    {
        $sql = 'SELECT sum(truncate(c.content,2)) as sumCanvass FROM  ';
        $sql .= ProdAttrInst::tableName();
        $sql .= ' as a, ';
        $sql .= ProdInst::tableName();
        $sql .= ' as b, ';
        $sql .= ProdAttrInst::tableName();

        if ($prod_spec_id==10002) {
            $sql .= ' as c WHERE a.attr_spec_id = :attr_spec_id AND a.prod_id = b.id and b.id = :id and a.content = :content and c.attr_spec_id = :attr_spec_id_k and c.prod_id = b.id';

            $canvassAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':attr_spec_id', '9')
                ->bindValue(':id',$id)
                ->bindValue(':content', '4001')
                ->bindValue(':attr_spec_id_k', '8')
                ->queryOne();
        }
        else{
            $sql .= ' as c WHERE a.attr_spec_id = :attr_spec_id AND a.prod_id = b.id and b.parent_id = :parent_id and a.content = :content and c.attr_spec_id = :attr_spec_id_k and c.prod_id = b.id';

            $canvassAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':attr_spec_id', '9')
                ->bindValue(':parent_id',$id)
                ->bindValue(':content', '4001')
                ->bindValue(':attr_spec_id_k', '8')
                ->queryOne();

        }



        return $canvassAreas;
        //return $this->areas;
    }

    // 在租均价
    public function getLendedPrices($id, $prod_spec_id=null)
    {
        $sql  = 'SELECT truncate(sum(truncate(content,2))/count(*),2) as sumLendedPrices FROM  ';
        $sql .= ProdAttrInst::tableName();
        $sql .= ' as a, ';
        $sql .= ProdInst::tableName() ;

        if ($prod_spec_id==10002) {
            $sql .= ' as b WHERE a.attr_spec_id =:attr_spec_id AND a.prod_id = b.id and b.id = :id and b.state = :state ';
            $lendedPrices = Yii::$app->db->createCommand($sql)
                ->bindValue(':attr_spec_id', 7)
                ->bindValue(':id',$id)
                ->bindValue(':state', '1002')
                ->queryOne();

        }else{
            $sql .= ' as b WHERE a.attr_spec_id =:attr_spec_id AND a.prod_id = b.id and b.parent_id = :parent_id and b.state = :state ';
            $lendedPrices = Yii::$app->db->createCommand($sql)
                ->bindValue(':attr_spec_id', 7)
                ->bindValue(':parent_id',$id)
                ->bindValue(':state', '1002')
                ->queryOne();
        }


        return $lendedPrices;
    }

    public function getOwnCusts()
    {
        return $this->hasOne(Cust::className(), ['id' => 'own_cust_id'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getUseCusts()
    {
        return $this->hasOne(Cust::className(), ['id' => 'use_cust_id'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getUseStaffs()//房源归属（ID用commonality默认-1）
    {
        return $this->hasOne(Staff::className(), ['user_id' => 'commonality'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }


    public function getCusts()
    {
        return $this->hasOne(Cust::className(), ['id' => 'own_cust_id'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getStraffs()
    {
        return $this->hasOne(Staff::className(), ['user_id' => 'create_staff_id'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }


    public function getUsers()
    {
        return $this->hasOne(User::className(), ['id' => 'create_staff_id'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getModifyUsers()
    {
        return $this->hasOne(User::className(), ['id' => 'modify_staff_id'] );
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getSuperParentRegions()
    {
        return $this->hasOne(Region::className(), ['id' => $this->parentRegions->parent_region_id]);
       // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getParentRegions()
    {
        return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }


    public function getContracts()
    {
        return $this->hasOne(Contract::className(), ['id' => 'contract_id'])->viaTable(TheSubject::tableName() . 'as o',  ['prod_id' => 'id']);
    }



    /*取出当前产品的子集*/
    public function getSubProdInsts()
    {
        return $this->hasMany(ProdInst::className(), ['parent_id' => 'id']);
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    /*取出当前产品的父集*/
    public function getParentProdInsts()
    {
        return $this->hasOne(ProdInst::className(), ['id' => 'parent_id']);
        // return $this->hasOne(Region::className(), ['id' => 'parent_region_id'])->viaTable(Region::tableName() . 'as d',  ['id' => 'region_id']);
    }

    public function getRegions()
    {
       // return $this->hasOne(Region::className(), ['region_id' => 'parent_region_id'])->viaTable(Region::className() . 'as c',  ['id' => 'region_id']);
        return $this->hasOne(Region::className(), ['id' => 'region_id']);
    }

    public function getProdAttrInsts()
    {
        /**
         * 第一个参数为要关联的字表模型类名称，
         *第二个参数指定 通过子表的 customer_id 去关联主表的 id 字段
         */
        return $this->hasMany(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInstsd()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);

    }
    public function getProdAttrInstsh()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInstsj()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInstsk()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);

    }
    public function getProdAttrInstsl()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInstsm()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInstsgw()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInststf()
    {
        //tf.
        $where['attr_spec_id'] = 80;
        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id'])->where($where);


    }
    public function getProdAttrInstsems()
    {
        //em.
        $where['attr_spec_id'] = 79;
        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id'])->where($where);


    }
    public function getProdAttrInstsetl()
    {
        //tl.
        $where['tl.attr_spec_id'] = 4;
        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id'])->where($where);


    }
    public function getProdAttrInstspm()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }

    public function getProdAttrInstsrs()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }
    public function getProdAttrInstspc()
    {

        return $this->hasOne(ProdAttrInst::className(), ['prod_id' => 'id']);


    }



    public function getCityList($parent_region_id=null)
    {
        $model = Region::find()->where(['parent_region_id' => $parent_region_id ])->orderBy('id')->all();


        return ArrayHelper::map($model, 'id', 'region_name');

    }

    public function getCustExternalAttrs()
    {
        /**
         * 第一个参数为要关联的字表模型类名称，
         *第二个参数指定 通过子表的 customer_id 去关联主表的 id 字段
         */
        return $this->hasMany(CustExternalAttr::className(), ['cust_id' => 'own_cust_id']);

    }


    public function getLocalProdAttrsInsts()
    {

        return $this->localProdAttrsInsts;
    }

    public function getAttrSpecs()
    {

        return $this->attrSpecs;
    }

    public function relations(){
        return array(

        'order_products' => array(self::HAS_MANY, 'OrderProduct', 'order_id'),
    );

    }
}

?>
